
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 06/15/2011 19:58:56
-- Generated from EDMX file: D:\Dokumenty\Studia magisterskie\Semestr 1\TSD\GradesApp\source\GradeApp\GradeApp.Model\GradeAppModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [D:\DOKUMENTY\STUDIA MAGISTERSKIE\SEMESTR 1\TSD\GRADESAPP\SOURCE\GRADEAPP\GRADEAPP.WEB\APP_DATA\GRADEAPPDB.MDF];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_CourseCourseConfiguration]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Courses] DROP CONSTRAINT [FK_CourseCourseConfiguration];
GO
IF OBJECT_ID(N'[dbo].[FK_CourseClass]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Classes] DROP CONSTRAINT [FK_CourseClass];
GO
IF OBJECT_ID(N'[dbo].[FK_CourseAssesmentForm]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AssesmentForms] DROP CONSTRAINT [FK_CourseAssesmentForm];
GO
IF OBJECT_ID(N'[dbo].[FK_StudentClassPresence]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ClassPresences] DROP CONSTRAINT [FK_StudentClassPresence];
GO
IF OBJECT_ID(N'[dbo].[FK_ClassClassPresence]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ClassPresences] DROP CONSTRAINT [FK_ClassClassPresence];
GO
IF OBJECT_ID(N'[dbo].[FK_StudentAssesmentResult]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AssesmentResults] DROP CONSTRAINT [FK_StudentAssesmentResult];
GO
IF OBJECT_ID(N'[dbo].[FK_AssesmentFormAssesmentResult]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AssesmentResults] DROP CONSTRAINT [FK_AssesmentFormAssesmentResult];
GO
IF OBJECT_ID(N'[dbo].[FK_TutorTutorCourse]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TutorCourses] DROP CONSTRAINT [FK_TutorTutorCourse];
GO
IF OBJECT_ID(N'[dbo].[FK_CourseTutorCourse]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TutorCourses] DROP CONSTRAINT [FK_CourseTutorCourse];
GO
IF OBJECT_ID(N'[dbo].[FK_CourseStudentCourse]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[StudentCourses] DROP CONSTRAINT [FK_CourseStudentCourse];
GO
IF OBJECT_ID(N'[dbo].[FK_StudentStudentCourse]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[StudentCourses] DROP CONSTRAINT [FK_StudentStudentCourse];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Students]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Students];
GO
IF OBJECT_ID(N'[dbo].[Courses]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Courses];
GO
IF OBJECT_ID(N'[dbo].[Classes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Classes];
GO
IF OBJECT_ID(N'[dbo].[AssesmentForms]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AssesmentForms];
GO
IF OBJECT_ID(N'[dbo].[CourseConfigurations]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CourseConfigurations];
GO
IF OBJECT_ID(N'[dbo].[ClassPresences]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ClassPresences];
GO
IF OBJECT_ID(N'[dbo].[AssesmentResults]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AssesmentResults];
GO
IF OBJECT_ID(N'[dbo].[Tutors]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Tutors];
GO
IF OBJECT_ID(N'[dbo].[TutorCourses]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TutorCourses];
GO
IF OBJECT_ID(N'[dbo].[StudentCourses]', 'U') IS NOT NULL
    DROP TABLE [dbo].[StudentCourses];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Students'
CREATE TABLE [dbo].[Students] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Surname] nvarchar(max)  NOT NULL,
    [Mail] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Courses'
CREATE TABLE [dbo].[Courses] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [ClassesNumber] int  NOT NULL,
    [CourseConfiguration_Id] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'Classes'
CREATE TABLE [dbo].[Classes] (
    [Id] uniqueidentifier  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [DateTime] datetime  NOT NULL,
    [CourseId] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'AssesmentForms'
CREATE TABLE [dbo].[AssesmentForms] (
    [Id] uniqueidentifier  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [MaxPoints] int  NOT NULL,
    [Type] nvarchar(max)  NOT NULL,
    [CourseId] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'CourseConfigurations'
CREATE TABLE [dbo].[CourseConfigurations] (
    [Id] uniqueidentifier  NOT NULL,
    [AbsencesAlarmPercent] int  NOT NULL,
    [PointsAlarmPercent] int  NOT NULL
);
GO

-- Creating table 'ClassPresences'
CREATE TABLE [dbo].[ClassPresences] (
    [WasPresent] bit  NOT NULL,
    [StudentId] uniqueidentifier  NOT NULL,
    [ClassId] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'AssesmentResults'
CREATE TABLE [dbo].[AssesmentResults] (
    [Points] nvarchar(max)  NOT NULL,
    [StudentId] uniqueidentifier  NOT NULL,
    [AssesmentFormId] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'Tutors'
CREATE TABLE [dbo].[Tutors] (
    [Id] uniqueidentifier  NOT NULL,
    [Mail] nvarchar(max)  NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'TutorCourses'
CREATE TABLE [dbo].[TutorCourses] (
    [IsOwner] bit  NOT NULL,
    [TutorId] uniqueidentifier  NOT NULL,
    [CourseId] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'StudentCourses'
CREATE TABLE [dbo].[StudentCourses] (
    [CourseId] uniqueidentifier  NOT NULL,
    [StudentId] uniqueidentifier  NOT NULL,
    [Id] uniqueidentifier  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Students'
ALTER TABLE [dbo].[Students]
ADD CONSTRAINT [PK_Students]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Courses'
ALTER TABLE [dbo].[Courses]
ADD CONSTRAINT [PK_Courses]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Classes'
ALTER TABLE [dbo].[Classes]
ADD CONSTRAINT [PK_Classes]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'AssesmentForms'
ALTER TABLE [dbo].[AssesmentForms]
ADD CONSTRAINT [PK_AssesmentForms]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'CourseConfigurations'
ALTER TABLE [dbo].[CourseConfigurations]
ADD CONSTRAINT [PK_CourseConfigurations]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [StudentId], [ClassId] in table 'ClassPresences'
ALTER TABLE [dbo].[ClassPresences]
ADD CONSTRAINT [PK_ClassPresences]
    PRIMARY KEY CLUSTERED ([StudentId], [ClassId] ASC);
GO

-- Creating primary key on [StudentId], [AssesmentFormId] in table 'AssesmentResults'
ALTER TABLE [dbo].[AssesmentResults]
ADD CONSTRAINT [PK_AssesmentResults]
    PRIMARY KEY CLUSTERED ([StudentId], [AssesmentFormId] ASC);
GO

-- Creating primary key on [Id] in table 'Tutors'
ALTER TABLE [dbo].[Tutors]
ADD CONSTRAINT [PK_Tutors]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [TutorId], [CourseId] in table 'TutorCourses'
ALTER TABLE [dbo].[TutorCourses]
ADD CONSTRAINT [PK_TutorCourses]
    PRIMARY KEY CLUSTERED ([TutorId], [CourseId] ASC);
GO

-- Creating primary key on [CourseId], [StudentId] in table 'StudentCourses'
ALTER TABLE [dbo].[StudentCourses]
ADD CONSTRAINT [PK_StudentCourses]
    PRIMARY KEY CLUSTERED ([CourseId], [StudentId] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [CourseConfiguration_Id] in table 'Courses'
ALTER TABLE [dbo].[Courses]
ADD CONSTRAINT [FK_CourseCourseConfiguration]
    FOREIGN KEY ([CourseConfiguration_Id])
    REFERENCES [dbo].[CourseConfigurations]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CourseCourseConfiguration'
CREATE INDEX [IX_FK_CourseCourseConfiguration]
ON [dbo].[Courses]
    ([CourseConfiguration_Id]);
GO

-- Creating foreign key on [CourseId] in table 'Classes'
ALTER TABLE [dbo].[Classes]
ADD CONSTRAINT [FK_CourseClass]
    FOREIGN KEY ([CourseId])
    REFERENCES [dbo].[Courses]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CourseClass'
CREATE INDEX [IX_FK_CourseClass]
ON [dbo].[Classes]
    ([CourseId]);
GO

-- Creating foreign key on [CourseId] in table 'AssesmentForms'
ALTER TABLE [dbo].[AssesmentForms]
ADD CONSTRAINT [FK_CourseAssesmentForm]
    FOREIGN KEY ([CourseId])
    REFERENCES [dbo].[Courses]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CourseAssesmentForm'
CREATE INDEX [IX_FK_CourseAssesmentForm]
ON [dbo].[AssesmentForms]
    ([CourseId]);
GO

-- Creating foreign key on [StudentId] in table 'ClassPresences'
ALTER TABLE [dbo].[ClassPresences]
ADD CONSTRAINT [FK_StudentClassPresence]
    FOREIGN KEY ([StudentId])
    REFERENCES [dbo].[Students]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [ClassId] in table 'ClassPresences'
ALTER TABLE [dbo].[ClassPresences]
ADD CONSTRAINT [FK_ClassClassPresence]
    FOREIGN KEY ([ClassId])
    REFERENCES [dbo].[Classes]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ClassClassPresence'
CREATE INDEX [IX_FK_ClassClassPresence]
ON [dbo].[ClassPresences]
    ([ClassId]);
GO

-- Creating foreign key on [StudentId] in table 'AssesmentResults'
ALTER TABLE [dbo].[AssesmentResults]
ADD CONSTRAINT [FK_StudentAssesmentResult]
    FOREIGN KEY ([StudentId])
    REFERENCES [dbo].[Students]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [AssesmentFormId] in table 'AssesmentResults'
ALTER TABLE [dbo].[AssesmentResults]
ADD CONSTRAINT [FK_AssesmentFormAssesmentResult]
    FOREIGN KEY ([AssesmentFormId])
    REFERENCES [dbo].[AssesmentForms]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AssesmentFormAssesmentResult'
CREATE INDEX [IX_FK_AssesmentFormAssesmentResult]
ON [dbo].[AssesmentResults]
    ([AssesmentFormId]);
GO

-- Creating foreign key on [TutorId] in table 'TutorCourses'
ALTER TABLE [dbo].[TutorCourses]
ADD CONSTRAINT [FK_TutorTutorCourse]
    FOREIGN KEY ([TutorId])
    REFERENCES [dbo].[Tutors]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [CourseId] in table 'TutorCourses'
ALTER TABLE [dbo].[TutorCourses]
ADD CONSTRAINT [FK_CourseTutorCourse]
    FOREIGN KEY ([CourseId])
    REFERENCES [dbo].[Courses]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CourseTutorCourse'
CREATE INDEX [IX_FK_CourseTutorCourse]
ON [dbo].[TutorCourses]
    ([CourseId]);
GO

-- Creating foreign key on [CourseId] in table 'StudentCourses'
ALTER TABLE [dbo].[StudentCourses]
ADD CONSTRAINT [FK_CourseStudentCourse]
    FOREIGN KEY ([CourseId])
    REFERENCES [dbo].[Courses]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [StudentId] in table 'StudentCourses'
ALTER TABLE [dbo].[StudentCourses]
ADD CONSTRAINT [FK_StudentStudentCourse]
    FOREIGN KEY ([StudentId])
    REFERENCES [dbo].[Students]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StudentStudentCourse'
CREATE INDEX [IX_FK_StudentStudentCourse]
ON [dbo].[StudentCourses]
    ([StudentId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------